﻿using System;
using System.Web;
using System.Web.Http;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO.Packaging;
using DocumentFormat.OpenXml.Office2016.Excel;
using System.Net;
using DocumentFormat.OpenXml.Drawing;
using System.Threading.Tasks;
using System.Threading;

namespace WTAppletProjectHTTPS.Models
{
    public class ExcelFile
    {

        public static ExcelPackage package = null;

        public static double TotalDataNum = 0;
        public static double DQNum = 0;

        /// <summary>
        /// string StartTime = "2024-07-05", string EndTime = "2024-07-20", string FormType = "全部", string WhereTJ = "WHERE 1=1"
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="FormType"></param>
        /// <param name="WhereTJ"></param>
        public static void ExcelExportFile(ExcelTableList TL)
        {

            if (ExcelTaskQueue.Tasks.TryGetValue(TL.TaskId, out var task))
            {
                task.Status = WTAppletProjectHTTPS.Models.TaskStatus.Pending;
                task.ErrorMessage = "任务开始";
                try
                {
                    // Set the license context for EPPlus
                    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                    // 获取图片的服务器路径
                    var imagePath = TL.HC.Server.MapPath("~/Images/小程序二维码.png");
                    //var imagePath = "E:/重要资料文件/万州同盛小程序项目/小程序图片/小程序二维码.png";
                    // 创建或打开Excel文件
                    package = new ExcelPackage();
                    // 添加一个新的工作表
                    var worksheet = package.Workbook.Worksheets.Add("TS Cola 生产自检 QC首件巡检终检记录");

                    // 设置行高
                    worksheet.Row(1).Height = 115;
                    worksheet.Row(2).Height = 85;
                    worksheet.Row(3).Height = 58;
                    worksheet.Row(4).Height = 40;

                    // 设置列宽
                    worksheet.Column(1).Width = 16;
                    worksheet.Column(2).Width = 30;
                    worksheet.Column(3).Width = 20;
                    worksheet.Column(4).Width = 39;
                    worksheet.Column(5).Width = 20;
                    worksheet.Column(6).Width = 40;

                    // 合并单元格并设置居中
                    worksheet.Cells["A1:F1"].Merge = true;
                    worksheet.Cells["A1:F1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells["A1:F1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells["A1:F1"].Style.WrapText = true;

                    // 设置标题和说明文字在A1单元格
                    var titleCell = worksheet.Cells["A1"];
                    titleCell.RichText.Add("数据说明和操作教程：").Size = 18;
                    titleCell.RichText[0].Color = Color.Blue;

                    titleCell.RichText.Add("\nsheet1为二维码的动态数据概况").Size = 14;
                    titleCell.RichText[1].Color = Color.Black;

                    titleCell.RichText.Add("\n可在下方切换表格sheet查看不同表单的明细数据").Size = 14;
                    titleCell.RichText[2].Color = Color.Black;

                    titleCell.RichText.Add("\n打印前需删除本行，直接页面打印即可存档").Size = 14;
                    titleCell.RichText[3].Color = Color.Red;

                    // 设置背景色
                    titleCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    titleCell.Style.Fill.BackgroundColor.SetColor(Color.White);
                    titleCell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    titleCell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    titleCell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    titleCell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    titleCell.Style.Font.Name = "宋体";

                    // 插入图片
                    var picture = worksheet.Drawings.AddPicture("QRCode", new FileInfo(imagePath));
                    picture.SetPosition(1, 0, 0, 0);
                    picture.SetSize(185, 190); // 设置图片宽高

                    // 合并单元格并设置表格标题
                    worksheet.Cells["A2:F2"].Merge = true;
                    worksheet.Cells["A2:F2"].Value = "TS Cola 生产自检 QC首件巡检终检记录";
                    worksheet.Cells["A2:F2"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells["A2:F2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells["A2:F2"].Style.Font.Size = 28;
                    worksheet.Cells["A2:F2"].Style.Font.Bold = true;
                    worksheet.Cells["A2:F2"].Style.Font.Name = "宋体";
                    worksheet.Cells["A2:F2"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A2:F2"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A2:F2"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A2:F2"].Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    worksheet.Cells["A3:F3"].Merge = true;
                    var dynamicDataCell = worksheet.Cells["A3:F3"];
                    dynamicDataCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    dynamicDataCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    dynamicDataCell.Style.WrapText = true;
                    dynamicDataCell.Style.Font.Name = "宋体";
                    // 设置单元格边框
                    dynamicDataCell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    dynamicDataCell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    dynamicDataCell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    dynamicDataCell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                    // 设置表头
                    worksheet.Row(4).Height = 64;
                    worksheet.Cells["A4:F4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    worksheet.Cells["A4:F4"].Style.Fill.BackgroundColor.SetColor(Color.WhiteSmoke);
                    worksheet.Cells["A4:F4"].Style.Font.Bold = true;
                    worksheet.Cells["A4:F4"].Style.Font.Name = "宋体";
                    worksheet.Cells["A4:F4"].Style.Font.Size = 15;
                    worksheet.Cells["A4:F4"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells["A4:F4"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells["A4:F4"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A4:F4"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A4:F4"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    worksheet.Cells["A4:F4"].Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    worksheet.Cells["A4"].Value = "记录编号";
                    worksheet.Cells["B4"].Value = "操作时间";
                    worksheet.Cells["C4"].Value = "操作人";
                    worksheet.Cells["D4"].Value = "操作";
                    worksheet.Cells["E4:F4"].Merge = true; // 合并结果列
                    worksheet.Cells["E4:F4"].Value = "结果";
                    task.ErrorMessage = "开始导出Sheet页：TS Cola 生产自检 QC首件巡检终检记录";
                    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
                    {
                        connection.Open();
                        var query = "";

                        if (TL.FormType == "F2")
                        {
                            query = $@"select *,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY HeadDate desc) AS RowNum from (
select * from (select QCSID HeadID,QCSCreateTime HeadDate,QCSCreateCreator HeadCreator,'QC抽检成品边框' as HeadFormName,'' as ResultMessage   
from QCSamplingFinishedFrame where QCSCreateTime IS NOT NULL AND QCSFormType = 'TS A1线 面盖物料确认' ) CPFT 
union 
select * from (select FFCID HeadID,FFCCreateTime HeadDate,FFCCreateCreator HeadCreator,'A1/A2员工边框上料确认' as HeadFormName,'' as ResultMessage  
from A1A2EmployeeFrameFeedingConfirmation where FFCCreateTime IS NOT NULL AND FFCFormType = 'TS A1线 面盖物料确认') CPFT ) Tatol {TL.WhereStrValue}";
                        }
                        else
                        {
                            query = $@"
select *,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY HeadDate desc) AS RowNum from (
select * from (select PADID HeadID,PADCreateTime HeadDate,PADCreateCreator HeadCreator,'成品关联模组生产日期' as HeadFormName,'' as ResultMessage 
from ProductAssociationDate where PADCreateTime IS NOT NULL) PAD
union 
select * from (select TSCMCID HeadID,TSCMCCreateTime HeadDate,TSCMCCreateCreator HeadCreator,'测试站换型物料确认' as HeadFormName,'' as ResultMessage  
from TestStationChangeMaterialConfirmation where TSCMCCreateTime IS NOT NULL) TSCMC
union
select * from (select FSCMCID HeadID,FSCMCCreateTime HeadDate,FSCMCCreateCreator HeadCreator,'扣边框站换型物料确认' as HeadFormName,'' as ResultMessage  
from FrameStationChangeMaterialConfirmation where FSCMCCreateTime IS NOT NULL) FSCMC
union 
select * from (select LMSSCID HeadID,LMSSCCreateTime HeadDate,LMSSCCreateCreator HeadCreator,'镭雕站换型自检确认' as HeadFormName,'' as ResultMessage  
from LaserMarkingStationSelfInspectionConfirmation where LMSSCCreateTime IS NOT NULL ) LMSSC
union
select * from (select PSCICID HeadID,PSCICCreateTime HeadDate,PSCICCreateCreator HeadCreator,'包装站换型信息确认' as HeadFormName,'' as ResultMessage  
from PackagingStationChangeInformationConfirmation where PSCICCreateTime IS NOT NULL) PSCIC
union
select * from (select CPFIRID HeadID,CPFIRCreateTime HeadDate,CPFIRCreateCreator HeadCreator,'Cola 生产全检记录' as HeadFormName,'' as ResultMessage   
from ColaProductionFullInspectionRecord where CPFIRCreateTime IS NOT NULL ) CPFIR
union 
select * from (select CFPID HeadID,CFPCreateTime HeadDate,CFPCreateCreator HeadCreator,'Cola首件/巡检记录' as HeadFormName,'' as ResultMessage  
from ColaFirstPiecePatrolInspectionRecord where CFPCreateTime IS NOT NULL) CFP 
union 
select * from (select CFIRID HeadID,CFIRCreateTime HeadDate,CFIRCreateCreator HeadCreator,'Cola终检检验记录' as HeadFormName,'' as ResultMessage  
from ColaFinalInspectionRecord where CFIRCreateTime IS NOT NULL) CFIR 
union 
select * from (select CPFTID HeadID,CPFTCreateTime HeadDate,CPFTCreateCreator HeadCreator,'Cola 产品功能测试' as HeadFormName,'' as ResultMessage  
from ColaProductFunctionTest where CPFTCreateTime IS NOT NULL) CPFT 
union 
select * from (select CPFIID HeadID,CPFICreateTime HeadDate,CPFICreateCreator HeadCreator,'Cola产线包装后全检' as HeadFormName,'' as ResultMessage  
from ColaPackingFinalInspection where CPFICreateTime IS NOT NULL) CPFT 
union 
select * from (select QCSID HeadID,QCSCreateTime HeadDate,QCSCreateCreator HeadCreator,'QC抽检成品边框' as HeadFormName,'' as ResultMessage   
from QCSamplingFinishedFrame where QCSCreateTime IS NOT NULL AND QCSFormType = 'TS Cola生产自检' ) CPFT 
union 
select * from (select FFCID HeadID,FFCCreateTime HeadDate,FFCCreateCreator HeadCreator,'A1/A2员工边框上料确认' as HeadFormName,'' as ResultMessage  
from A1A2EmployeeFrameFeedingConfirmation where FFCCreateTime IS NOT NULL AND FFCFormType = 'TS Cola生产自检') CPFT ) Tatol {TL.WhereStrValue}";
                        }
                        var parameters = new List<SqlParameter>
                        {

                        };
                        using (var command = new SqlCommand(query, connection))
                        {
                            command.Parameters.AddRange(parameters.ToArray());
                            using (var reader = command.ExecuteReader())
                            {
                                int currentRow = 5;
                                while (reader.Read())
                                {

                                    if (currentRow == 5)
                                    {
                                        TotalDataNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                    }
                                    DQNum += 1;
                                    task.ProgressPercentage = Math.Round((double)(DQNum / (TotalDataNum * 2)), 2) * 100;
                                    worksheet.Row(currentRow).Height = 64;
                                    worksheet.Cells[currentRow, 1].Value = "L" + reader.GetInt32(reader.GetOrdinal("HeadID")).ToString("0000000");
                                    worksheet.Cells[currentRow, 2].Value = reader.GetDateTime(reader.GetOrdinal("HeadDate")).ToString("yyyy-MM-dd HH:mm:ss");
                                    worksheet.Cells[currentRow, 3].Value = reader.GetString(reader.GetOrdinal("HeadCreator"));
                                    worksheet.Cells[currentRow, 4].Value = "填写了" + reader.GetString(reader.GetOrdinal("HeadFormName"));
                                    worksheet.Cells[currentRow, 5, currentRow, 6].Merge = true; // 合并结果列
                                    worksheet.Cells[currentRow, 5].Value = reader.GetString(reader.GetOrdinal("ResultMessage"));
                                    worksheet.Cells[currentRow, 1, currentRow, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                    worksheet.Cells[currentRow, 1, currentRow, 6].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                    worksheet.Cells[currentRow, 1, currentRow, 6].Style.Font.Name = "宋体";
                                    worksheet.Cells[currentRow, 1, currentRow, 6].Style.Font.Size = 15;
                                    // 设置数据单元格边框
                                    var dataRange = worksheet.Cells[currentRow, 1, currentRow, 6];
                                    dataRange.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                                    dataRange.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                                    dataRange.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                                    dataRange.Style.Border.Right.Style = ExcelBorderStyle.Thin;
                                    currentRow++;
                                }
                            }
                        }
                    };
                    task.ErrorMessage = "Sheet页：TS Cola 生产自检 QC首件巡检终检记录，导出完成";
                    if (TL.OpeationType == "全部")
                    {
                        List<string> lst = new List<string>();
                        if (TL.FormType == "F2")
                        {
                            lst.Add("D595QC抽检成品边框");
                            lst.Add("D594A1/A2员工边框上料确认(自动)");
                        }
                        else
                        {
                            lst.Add("D574成品关联模组生产日期");
                            lst.Add("D553模组测试站换型物料自检确认");
                            lst.Add("D554扣边框站换型物料自检确认(手工)");
                            lst.Add("D555镭雕站换型自检确认");
                            lst.Add("D556包装工位信息确认");
                            lst.Add("D560例行检验(生产全检记录)");
                            lst.Add("D558Cola 首件巡检记录");
                            lst.Add("D559Cola 终检检验记录");
                            lst.Add("D587Cola 产品功能测试");
                            lst.Add("D562包装后全检记录");
                            lst.Add("D595QC抽检成品边框");
                            lst.Add("D594A1/A2员工边框上料确认(自动)");
                        }
                        
                        for (int i = 0; i < lst.Count; i++)
                        {

                            switch (lst[i])
                            {
                                case "D574成品关联模组生产日期":
                                    task.ErrorMessage = "开始导出Sheet页：D574成品关联模组生产日期";
                                    ProductAssociationDate(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D574成品关联模组生产日期，导出完成";
                                    break;
                                case "D553模组测试站换型物料自检确认":
                                    task.ErrorMessage = "开始导出Sheet页：D553模组测试站换型物料自检确认";
                                    TestStationChangeMaterialConfirmation(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D553模组测试站换型物料自检确认，导出完成";
                                    break;
                                case "D554扣边框站换型物料自检确认(手工)":
                                    task.ErrorMessage = "开始导出Sheet页：D554扣边框站换型物料自检确认(手工)";
                                    FrameStationChangeMaterialConfirmation(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D554扣边框站换型物料自检确认，导出完成";
                                    break;
                                case "D555镭雕站换型自检确认":
                                    task.ErrorMessage = "开始导出Sheet页：D555镭雕站换型自检确认";
                                    LaserMarkingStationSelfInspectionConfirmation(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D555镭雕站换型自检确认，导出完成";
                                    break;
                                case "D556包装工位信息确认":
                                    task.ErrorMessage = "开始导出Sheet页：D556包装工位信息确认";
                                    PackagingStationChangeInformationConfirmation(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D556包装工位信息确认，导出完成";
                                    break;
                                case "D560例行检验(生产全检记录)":
                                    task.ErrorMessage = "开始导出Sheet页：D560例行检验(生产全检记录)";
                                    ColaProductionFullInspectionRecord(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D560例行检验(生产全检记录)，导出完成";
                                    break;
                                case "D558Cola 首件巡检记录":
                                    task.ErrorMessage = "开始导出Sheet页：D558Cola 首件巡检记录";
                                    ColaFirstPiecePatrolInspectionRecord(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D558Cola 首件巡检记录，导出完成";
                                    break;
                                case "D559Cola 终检检验记录":
                                    task.ErrorMessage = "开始导出Sheet页：D559Cola 终检检验记录";
                                    ColaFinalInspectionRecord(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D559Cola 终检检验记录，导出完成";
                                    break;
                                case "D587Cola 产品功能测试":
                                    task.ErrorMessage = "开始导出Sheet页：D587Cola 产品功能测试";
                                    ColaProductFunctionTest(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D587Cola 产品功能测试，导出完成";
                                    break;
                                case "D562包装后全检记录":
                                    task.ErrorMessage = "开始导出Sheet页：D562包装后全检记录";
                                    ColaPackingFinalInspection(TL.StartDate, TL.EndDate);
                                    task.ErrorMessage = "Sheet页：D562包装后全检记录，导出完成";
                                    break;
                                case "D595QC抽检成品边框":
                                    task.ErrorMessage = "开始导出Sheet页：D595QC抽检成品边框";
                                    QCSamplingFinishedFrame(TL.StartDate, TL.EndDate,TL.FormType);
                                    task.ErrorMessage = "Sheet页：D595QC抽检成品边框，导出完成";
                                    break;
                                case "D594A1/A2员工边框上料确认(自动)":
                                    task.ErrorMessage = "开始导出Sheet页：D594A1/A2员工边框上料确认(自动)";
                                    A1A2EmployeeFrameFeedingConfirmation(TL.StartDate, TL.EndDate, TL.FormType);
                                    task.ErrorMessage = "Sheet页：D594A1/A2员工边框上料确认(自动)，导出完成";
                                    break;
                                default:
                                    break;
                            }
                            task.ProgressPercentage = Math.Round((double)(DQNum / (TotalDataNum * 2)), 2) * 100;
                        }
                    }
                    else
                    {
                        switch (TL.OpeationType)
                        {
                            case "D574成品关联模组生产日期":
                                task.ErrorMessage = "开始导出Sheet页：D574成品关联模组生产日期";
                                ProductAssociationDate(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D574成品关联模组生产日期，导出完成";
                                break;
                            case "D553模组测试站换型物料自检确认":
                                task.ErrorMessage = "开始导出Sheet页：D553模组测试站换型物料自检确认";
                                TestStationChangeMaterialConfirmation(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D553模组测试站换型物料自检确认，导出完成";
                                break;
                            case "D554扣边框站换型物料自检确认(手工)":
                                task.ErrorMessage = "开始导出Sheet页：D554扣边框站换型物料自检确认(手工)";
                                FrameStationChangeMaterialConfirmation(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D554扣边框站换型物料自检确认，导出完成";
                                break;
                            case "D555镭雕站换型自检确认":
                                task.ErrorMessage = "开始导出Sheet页：D555镭雕站换型自检确认";
                                LaserMarkingStationSelfInspectionConfirmation(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D555镭雕站换型自检确认，导出完成";
                                break;
                            case "D556包装工位信息确认":
                                task.ErrorMessage = "开始导出Sheet页：D556包装工位信息确认";
                                PackagingStationChangeInformationConfirmation(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D556包装工位信息确认，导出完成";
                                break;
                            case "D560例行检验(生产全检记录)":
                                task.ErrorMessage = "开始导出Sheet页：D560例行检验(生产全检记录)";
                                ColaProductionFullInspectionRecord(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D560例行检验(生产全检记录)，导出完成";
                                break;
                            case "D558Cola 首件巡检记录":
                                task.ErrorMessage = "开始导出Sheet页：D558Cola 首件巡检记录";
                                ColaFirstPiecePatrolInspectionRecord(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D558Cola 首件巡检记录，导出完成";
                                break;
                            case "D559Cola 终检检验记录":
                                task.ErrorMessage = "开始导出Sheet页：D559Cola 终检检验记录";
                                ColaFinalInspectionRecord(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D559Cola 终检检验记录，导出完成";
                                break;
                            case "D587Cola 产品功能测试":
                                task.ErrorMessage = "开始导出Sheet页：D587Cola 产品功能测试";
                                ColaProductFunctionTest(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D587Cola 产品功能测试，导出完成";
                                break;
                            case "D562包装后全检记录":
                                task.ErrorMessage = "开始导出Sheet页：D562包装后全检记录";
                                ColaPackingFinalInspection(TL.StartDate, TL.EndDate);
                                task.ErrorMessage = "Sheet页：D562包装后全检记录，导出完成";
                                break;
                            case "D595QC抽检成品边框":
                                task.ErrorMessage = "开始导出Sheet页：D595QC抽检成品边框";
                                QCSamplingFinishedFrame(TL.StartDate, TL.EndDate,TL.FormType);
                                task.ErrorMessage = "Sheet页：D595QC抽检成品边框，导出完成";
                                break;
                            case "D594A1/A2员工边框上料确认(自动)":
                                task.ErrorMessage = "开始导出Sheet页：D594A1/A2员工边框上料确认(自动)";
                                A1A2EmployeeFrameFeedingConfirmation(TL.StartDate, TL.EndDate, TL.FormType);
                                task.ErrorMessage = "Sheet页：D594A1/A2员工边框上料确认(自动)，导出完成";
                                break;
                            default:
                                break;
                        }
                        task.ProgressPercentage = Math.Round((double)(DQNum / (TotalDataNum * 2)), 2) * 100;
                    }
                    var richText = dynamicDataCell.RichText;
                    if (TL.StartDate == "全部")
                    {
                        ExcelRichText ERT = richText.Add("动态数据\n");
                        ExcelRichText ERT1 = richText.Add("(全部数据 共" + TotalDataNum + "条)");
                        ERT.Size = 20;
                        ERT.Bold = true;
                        ERT1.Size = 14;
                    }
                    else
                    {
                        ExcelRichText ERT = richText.Add("动态数据\n");
                        ExcelRichText ERT1 = richText.Add("(" + TL.StartDate + " 至 " + TL.EndDate + " 共" + TotalDataNum + "条)");
                        ERT.Size = 20;
                        ERT.Bold = true;
                        ERT1.Size = 14;
                    }

                    var filePath = TL.HC.Server.MapPath("~/TS_Cola_Inspection_Report" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
                    //var filePath = "E:/VS自建项目工程/WTAppletProjectHTTPS/WTAppletProjectHTTPS/TS_Cola_Inspection_Report" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    // 保存文件
                    var filePathnew = $"{TL.HC.Request.Url.Scheme}://{TL.HC.Request.Url.Host}:{TL.HC.Request.Url.Port}/TS_Cola_Inspection_Report" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                    package.SaveAs(new FileInfo(filePath));

                    task.FilePath = filePathnew;
                    task.Status = TaskStatus.Completed;
                    task.ProgressPercentage = 100;
                    TotalDataNum = 0;
                    DQNum = 0;
                    package.Dispose();
                }
                catch (Exception ex)
                {
                    if (package == null)
                    {
                        package.Dispose();
                    }
                    task.Status = TaskStatus.Failed;
                    task.ErrorMessage = ex.Message;
                    TotalDataNum = 0;
                    DQNum = 0;
                }
            }
        }

        /// <summary>
        /// 成品关联模组生产日期  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void ProductAssociationDate(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D574成品关联模组生产日期"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D574成品关联模组生产日期");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 19; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:S1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:S1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:S1"].Style.Font.Bold = true;
            worksheet.Cells["A1:S1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:S1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:S1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:S1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:S1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:S1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:S1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:S1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";

            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "日期";
            worksheet.Cells["H1"].Value = "线体";
            worksheet.Cells["I1"].Value = "产品信息";
            worksheet.Cells["J1"].Value = "模组生产日期拍照";
            worksheet.Cells["K1"].Value = "图片链接";
            worksheet.Cells["L1"].Value = "处理进度";
            worksheet.Cells["M1"].Value = "处理人";
            worksheet.Cells["N1"].Value = "处理时间";
            worksheet.Cells["O1"].Value = "后续动态";
            worksheet.Cells["P1"].Value = "来源";
            worksheet.Cells["Q1"].Value = "主记录编号";
            worksheet.Cells["R1"].Value = "填表人";
            worksheet.Cells["S1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND PADCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select PADID HeadID,PADCreateTime HeadDate,'成品关联模组生产日期' as HeadFormName,'D574' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,PADDate,PADLineBody,PADProductInformation,
'在最后图片链接里查看' as MZPhoto,PADModuleProductionDatePhotoScan ImagesPath,'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,PADCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY PADCreateTime desc) AS RowNum  
from ProductAssociationDate {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = DateTimeBackValue(reader, "PADDate");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "PADLineBody");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "PADProductInformation");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "MZPhoto");
                                ExcelHyperlink(worksheet.Cells[currentRow, 11], reader, "ImagesPath");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "PADCreateCreator");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "FillingMethod");

                                worksheet.Cells[currentRow, 1, currentRow, 19].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 19].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 19].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 19].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }
        /// <summary>
        /// 测试站换型物料确认  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void TestStationChangeMaterialConfirmation(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D553模组测试站换型物料自检确认"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D553模组测试站换型物料自检确认");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 23; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:W1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:W1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:W1"].Style.Font.Bold = true;
            worksheet.Cells["A1:W1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:W1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:W1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:W1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:W1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:W1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:W1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:W1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "作业员姓名";
            worksheet.Cells["H1"].Value = "时间";
            worksheet.Cells["I1"].Value = "线别";
            worksheet.Cells["J1"].Value = "成品型号";
            worksheet.Cells["K1"].Value = "模组型号";
            worksheet.Cells["L1"].Value = "模组正确性核对";
            worksheet.Cells["M1"].Value = "不符合描述（若有）";
            worksheet.Cells["N1"].Value = "不符合图片";
            worksheet.Cells["O1"].Value = "图片链接";
            worksheet.Cells["P1"].Value = "处理进度";
            worksheet.Cells["Q1"].Value = "处理人";
            worksheet.Cells["R1"].Value = "处理时间";
            worksheet.Cells["S1"].Value = "后续动态";
            worksheet.Cells["T1"].Value = "来源";
            worksheet.Cells["U1"].Value = "主记录编号";
            worksheet.Cells["V1"].Value = "填表人";
            worksheet.Cells["W1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND TSCMCCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select TSCMCID HeadID,TSCMCCreateTime HeadDate,'测试站换型物料确认' as HeadFormName,'D553' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,TSCMCOperatorName,TSCMCConfirmationTime,TSCMCLineParting,
TSCMCProductModel,TSCMCModuleModel,TSCMCModuleCorrectnessCheck,TSCMCNonconformanceDescription,'在最后图片链接里查看' AS NoImageMessae,TSCMCNonconformanceImage,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,TSCMCCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY TSCMCCreateTime desc) AS RowNum  
from TestStationChangeMaterialConfirmation {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "TSCMCOperatorName");
                                worksheet.Cells[currentRow, 8].Value = DateTimeBackValue(reader, "TSCMCConfirmationTime");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "TSCMCLineParting");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "TSCMCProductModel");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "TSCMCModuleModel");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "TSCMCModuleCorrectnessCheck");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "TSCMCNonconformanceDescription");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "NoImageMessae");
                                ExcelHyperlink(worksheet.Cells[currentRow, 15], reader, "TSCMCNonconformanceImage");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "TSCMCCreateCreator");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "FillingMethod");

                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }

        /// <summary>
        /// 扣边框站换型物料确认  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void FrameStationChangeMaterialConfirmation(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D554扣边框站换型物料自检确认(手工)"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D554扣边框站换型物料自检确认(手工)");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 27; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:AA1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:AA1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:AA1"].Style.Font.Bold = true;
            worksheet.Cells["A1:AA1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:AA1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:AA1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:AA1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:AA1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AA1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AA1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AA1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "作业员姓名";
            worksheet.Cells["H1"].Value = "确认时间";
            worksheet.Cells["I1"].Value = "成品型号";
            worksheet.Cells["J1"].Value = "线别";
            worksheet.Cells["K1"].Value = "零件符合特性";
            worksheet.Cells["L1"].Value = "边框logo位置";
            worksheet.Cells["M1"].Value = "边框颜色";
            worksheet.Cells["N1"].Value = "按钮logo位置";
            worksheet.Cells["O1"].Value = "荧光粒颜色";
            worksheet.Cells["P1"].Value = "不符合特性（若有）";
            worksheet.Cells["Q1"].Value = "不符合图片";
            worksheet.Cells["R1"].Value = "图片链接";
            worksheet.Cells["S1"].Value = "填写方式";
            worksheet.Cells["T1"].Value = "处理进度";
            worksheet.Cells["U1"].Value = "处理人";
            worksheet.Cells["V1"].Value = "处理时间";
            worksheet.Cells["W1"].Value = "后续动态";
            worksheet.Cells["X1"].Value = "来源";
            worksheet.Cells["Y1"].Value = "主记录编号";
            worksheet.Cells["Z1"].Value = "填表人";
            worksheet.Cells["AA1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND FSCMCCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select FSCMCID HeadID,FSCMCCreateTime HeadDate,'扣边框站换型物料确认' as HeadFormName,'D554' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
FSCMCOperatorConfirmation,
FSCMCConfirmationTime,
FSCMCProductModel,
FSCMCLineParting,
'零件符合特性' as FSCMCPartCompliance,
FSCMCFrameLOGOPosition,
FSCMCFrameColor,
FSCMCButtonLOGOPosition,
FSCMCButtonColor,
FSCMCFluorescentParticleColor,
FSCMCNonconformanceDescriptionIfAny,
'在最后图片链接里查看' AS NoImageMessae,
FSCMCNonconformanceImage,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,FSCMCCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY FSCMCCreateTime desc) AS RowNum  
from FrameStationChangeMaterialConfirmation {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "FSCMCOperatorConfirmation");
                                worksheet.Cells[currentRow, 8].Value = DateTimeBackValue(reader, "FSCMCConfirmationTime");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "FSCMCProductModel");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "FSCMCLineParting");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "FSCMCPartCompliance");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "FSCMCFrameLOGOPosition");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "FSCMCFrameColor");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "FSCMCButtonLOGOPosition");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "FSCMCButtonColor");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "FSCMCFluorescentParticleColor");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "FSCMCNonconformanceDescriptionIfAny");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "NoImageMessae");
                                ExcelHyperlink(worksheet.Cells[currentRow, 19], reader, "FSCMCNonconformanceImage");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 24].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 25].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 26].Value = StringBackValue(reader, "FSCMCCreateCreator");
                                worksheet.Cells[currentRow, 27].Value = StringBackValue(reader, "FillingMethod");
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }
        /// <summary>
        /// 镭雕站换型自检确认  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void LaserMarkingStationSelfInspectionConfirmation(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D555镭雕站换型自检确认"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D555镭雕站换型自检确认");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 20; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:T1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:T1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:T1"].Style.Font.Bold = true;
            worksheet.Cells["A1:T1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:T1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:T1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:T1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:T1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:T1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:T1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:T1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "作业员姓名";
            worksheet.Cells["H1"].Value = "确认时间";
            worksheet.Cells["I1"].Value = "线别";
            worksheet.Cells["J1"].Value = "成品vs镭雕型号";
            worksheet.Cells["K1"].Value = "不符合描述";
            worksheet.Cells["L1"].Value = "图片链接";
            worksheet.Cells["M1"].Value = "处理进度";
            worksheet.Cells["N1"].Value = "处理人";
            worksheet.Cells["O1"].Value = "处理时间";
            worksheet.Cells["P1"].Value = "后续动态";
            worksheet.Cells["Q1"].Value = "来源";
            worksheet.Cells["R1"].Value = "主记录编号";
            worksheet.Cells["S1"].Value = "填表人";
            worksheet.Cells["T1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND LMSSCCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select LMSSCID HeadID,LMSSCCreateTime HeadDate,'镭雕站换型自检确认' as HeadFormName,'D555' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
LMSSCOperatorName,
LMSSCConfirmationTime,
LMSSCLineParting,
'在最后图片链接里查看' AS LMSSCProductVSMarkingModel,
LMSSCNonconformanceDescriptionIfAny,
LMSSCNonconformanceImage,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,LMSSCCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY LMSSCCreateTime desc) AS RowNum  
from LaserMarkingStationSelfInspectionConfirmation {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "LMSSCOperatorName");
                                worksheet.Cells[currentRow, 8].Value = DateTimeBackValue(reader, "LMSSCConfirmationTime");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "LMSSCLineParting");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "LMSSCProductVSMarkingModel");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "LMSSCNonconformanceDescriptionIfAny");
                                ExcelHyperlink(worksheet.Cells[currentRow, 12], reader, "LMSSCNonconformanceImage");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "LMSSCCreateCreator");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "FillingMethod");
                                worksheet.Cells[currentRow, 1, currentRow, 20].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 20].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 20].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 20].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }

        /// <summary>
        /// 包装站换型信息确认  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void PackagingStationChangeInformationConfirmation(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D556包装工位信息确认"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D556包装工位信息确认");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 23; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:W1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:W1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:W1"].Style.Font.Bold = true;
            worksheet.Cells["A1:W1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:W1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:W1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:W1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:W1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:W1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:W1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:W1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "作业员姓名";
            worksheet.Cells["H1"].Value = "确认时间";
            worksheet.Cells["I1"].Value = "线别";
            worksheet.Cells["J1"].Value = "渠道";
            worksheet.Cells["K1"].Value = "胶带膜拍照";
            worksheet.Cells["L1"].Value = "热转印标签拍照";
            worksheet.Cells["M1"].Value = "彩盒拍照";
            worksheet.Cells["N1"].Value = "彩盒标签拍照";
            worksheet.Cells["O1"].Value = "外箱标签拍照";
            worksheet.Cells["P1"].Value = "处理进度";
            worksheet.Cells["Q1"].Value = "处理人";
            worksheet.Cells["R1"].Value = "处理时间";
            worksheet.Cells["S1"].Value = "后续动态";
            worksheet.Cells["T1"].Value = "来源";
            worksheet.Cells["U1"].Value = "主记录编号";
            worksheet.Cells["V1"].Value = "填表人";
            worksheet.Cells["W1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND PSCICCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select PSCICID HeadID,PSCICCreateTime HeadDate,'包装站换型信息确认' as HeadFormName,'D556' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
PSCICOperatorName,
PSCICConfirmationTime,
PSCICLineParting,
PSCICChannel,
PSCICPlasticBagFilmPhoto,
PSCICThermalTransferLabelPhoto,
PSCICColorBoxPhoto,
PSCICColorBoxLabelPhoto,
PSCICOuterBoxLabelPhoto,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,PSCICCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY PSCICCreateTime desc) AS RowNum  
from PackagingStationChangeInformationConfirmation {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "PSCICOperatorName");
                                worksheet.Cells[currentRow, 8].Value = DateTimeBackValue(reader, "PSCICConfirmationTime");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "PSCICLineParting");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "PSCICChannel");
                                ExcelHyperlink(worksheet.Cells[currentRow, 11], reader, "PSCICPlasticBagFilmPhoto");
                                ExcelHyperlink(worksheet.Cells[currentRow, 12], reader, "PSCICThermalTransferLabelPhoto");
                                ExcelHyperlink(worksheet.Cells[currentRow, 13], reader, "PSCICColorBoxPhoto");
                                ExcelHyperlink(worksheet.Cells[currentRow, 14], reader, "PSCICColorBoxLabelPhoto");
                                ExcelHyperlink(worksheet.Cells[currentRow, 15], reader, "PSCICOuterBoxLabelPhoto");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "PSCICCreateCreator");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "FillingMethod");

                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 23].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }
        /// <summary>
        /// Cola 生产全检记录  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void ColaProductionFullInspectionRecord(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D560例行检验(生产全检记录)"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D560例行检验(生产全检记录)");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 36; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:AJ1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:AJ1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:AJ1"].Style.Font.Bold = true;
            worksheet.Cells["A1:AJ1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:AJ1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:AJ1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:AJ1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:AJ1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AJ1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AJ1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AJ1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "生产日期";
            worksheet.Cells["H1"].Value = "线别";
            worksheet.Cells["I1"].Value = "生产型号";
            worksheet.Cells["J1"].Value = "订单号";
            worksheet.Cells["K1"].Value = "生产数量";
            worksheet.Cells["L1"].Value = "不良品数量";
            worksheet.Cells["M1"].Value = "扣脚断裂";
            worksheet.Cells["N1"].Value = "混错料";
            worksheet.Cells["O1"].Value = "镭雕不良";
            worksheet.Cells["P1"].Value = "漏垫片";
            worksheet.Cells["Q1"].Value = "手感不良";
            worksheet.Cells["R1"].Value = "成品外观不良";
            worksheet.Cells["S1"].Value = "素材外观不良";
            worksheet.Cells["T1"].Value = "喷油外观不良";
            worksheet.Cells["U1"].Value = "通电不良";
            worksheet.Cells["V1"].Value = "包装不良";
            worksheet.Cells["W1"].Value = "装备不良（记录结果）";
            worksheet.Cells["X1"].Value = "保护门不回弹";
            worksheet.Cells["Y1"].Value = "插头插不到底";
            worksheet.Cells["Z1"].Value = "耐压不良";
            worksheet.Cells["AA1"].Value = "标志不符";
            worksheet.Cells["AB1"].Value = "数字";
            worksheet.Cells["AC1"].Value = "处理进度";
            worksheet.Cells["AD1"].Value = "处理人";
            worksheet.Cells["AE1"].Value = "处理时间";
            worksheet.Cells["AF1"].Value = "后续动态";
            worksheet.Cells["AG1"].Value = "来源";
            worksheet.Cells["AH1"].Value = "主记录编号";
            worksheet.Cells["AI1"].Value = "填表人";
            worksheet.Cells["AJ1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND CPFIRCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select CPFIRID HeadID,CPFIRCreateTime HeadDate,'Cola 生产全检记录' as HeadFormName,'D560' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
CPFIRProductionDate,
CPFIRLineParting,
CPFIRProductionModel,
CPFIROrderNumber,
CPFIRProductionQuantity,
CPFIRDefectiveQuantity,
CPFIRBrokenFoot,
CPFIRMixedMaterials,
CPFIRLaserMarkingDefect,
CPFIRMissingGasket,
CPFIRPoorHandfeel,
CPFIRFinishedProductAppearance,
CPFIRMaterialAppearanceDefect,
CPFIRSprayPaintAppearanceDefect,
CPFIRDisconnectionDefect,
CPFIRPackagingDefect,
CPFIRAssemblyDefect,
CPFIRProtectiveDoorNotRebound,
CPFIRPlugNotFullyInserted,
CPFIRVoltageResistanceDefect,
CPFIRMarkingMismatch,
'0' as StrNumber,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,CPFIRCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY CPFIRCreateTime desc) AS RowNum  
from ColaProductionFullInspectionRecord {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = DateTimeBackValue(reader, "CPFIRProductionDate");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "CPFIRLineParting");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "CPFIRProductionModel");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "CPFIROrderNumber");
                                worksheet.Cells[currentRow, 11].Value = IntBackValue(reader, "CPFIRProductionQuantity");
                                worksheet.Cells[currentRow, 12].Value = IntBackValue(reader, "CPFIRDefectiveQuantity");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "CPFIRBrokenFoot");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "CPFIRMixedMaterials");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "CPFIRLaserMarkingDefect");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "CPFIRMissingGasket");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "CPFIRPoorHandfeel");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "CPFIRFinishedProductAppearance");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "CPFIRMaterialAppearanceDefect");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "CPFIRSprayPaintAppearanceDefect");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "CPFIRDisconnectionDefect");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "CPFIRPackagingDefect");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "CPFIRAssemblyDefect");
                                worksheet.Cells[currentRow, 24].Value = StringBackValue(reader, "CPFIRProtectiveDoorNotRebound");
                                worksheet.Cells[currentRow, 25].Value = StringBackValue(reader, "CPFIRPlugNotFullyInserted");
                                worksheet.Cells[currentRow, 26].Value = StringBackValue(reader, "CPFIRVoltageResistanceDefect");
                                worksheet.Cells[currentRow, 27].Value = StringBackValue(reader, "CPFIRMarkingMismatch");
                                worksheet.Cells[currentRow, 28].Value = StringBackValue(reader, "StrNumber");
                                worksheet.Cells[currentRow, 29].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 30].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 31].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 32].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 33].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 34].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 35].Value = StringBackValue(reader, "CPFIRCreateCreator");
                                worksheet.Cells[currentRow, 36].Value = StringBackValue(reader, "FillingMethod");
                                worksheet.Cells[currentRow, 1, currentRow, 36].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 36].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 36].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 36].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }

        /// <summary>
        /// Cola首件/巡检记录  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void ColaFirstPiecePatrolInspectionRecord(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D558Cola 首件巡检记录"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D558Cola 首件巡检记录");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 45; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:AS1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:AS1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:AS1"].Style.Font.Bold = true;
            worksheet.Cells["A1:AS1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:AS1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:AS1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:AS1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:AS1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AS1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AS1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AS1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "检验员";
            worksheet.Cells["H1"].Value = "班别";
            worksheet.Cells["I1"].Value = "检验时间";
            worksheet.Cells["J1"].Value = "线别";
            worksheet.Cells["K1"].Value = "检验时机";
            worksheet.Cells["L1"].Value = "产品型号";
            worksheet.Cells["M1"].Value = "订单号";
            worksheet.Cells["N1"].Value = "检验项目";
            worksheet.Cells["O1"].Value = "产品->标志";
            worksheet.Cells["P1"].Value = "产品->外观";
            worksheet.Cells["Q1"].Value = "产品->装配检查";
            worksheet.Cells["R1"].Value = "过程->人员资质";
            worksheet.Cells["S1"].Value = "过程->工装夹具";
            worksheet.Cells["T1"].Value = "过程->物料确认";
            worksheet.Cells["U1"].Value = "过程->方法确认";
            worksheet.Cells["V1"].Value = "产品->产品性能";
            worksheet.Cells["W1"].Value = "产品->通断测试";
            worksheet.Cells["X1"].Value = "产品->手感";
            worksheet.Cells["Y1"].Value = "产品->包装检测";
            worksheet.Cells["Z1"].Value = "产品->条码扫码";
            worksheet.Cells["AA1"].Value = "最终结论";
            worksheet.Cells["AB1"].Value = "第几次检验合格";
            worksheet.Cells["AC1"].Value = "不合格描述1（5W2H）";
            worksheet.Cells["AD1"].Value = "不合格图片1";
            worksheet.Cells["AE1"].Value = "不合格描述2（5W2H）";
            worksheet.Cells["AF1"].Value = "不合格图片2";
            worksheet.Cells["AG1"].Value = "不合格描述3（5W2H）";
            worksheet.Cells["AH1"].Value = "不合格图片3";
            worksheet.Cells["AI1"].Value = "不合格描述4（5W2H）";
            worksheet.Cells["AJ1"].Value = "不合格图片4";
            worksheet.Cells["AK1"].Value = "图片链接";
            worksheet.Cells["AL1"].Value = "处理进度";
            worksheet.Cells["AM1"].Value = "处理人";
            worksheet.Cells["AN1"].Value = "处理时间";
            worksheet.Cells["AO1"].Value = "后续动态";
            worksheet.Cells["AP1"].Value = "来源";
            worksheet.Cells["AQ1"].Value = "主记录编号";
            worksheet.Cells["AR1"].Value = "填表人";
            worksheet.Cells["AS1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND CFPCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select CFPID HeadID,CFPCreateTime HeadDate,'Cola首件/巡检记录' as HeadFormName,'D558' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
CFPInspector,
CFPShift,
CFPInspectionTime,
CFPLineParting,
CFPInspectionTiming,
CFPProductModel,
CFPOrderNumber,
CFPInspectionItem,
CFPProductMarking,
CFPProductAppearance,
CFPProductAssemblyInspection,
CFPProcessPersonnelQualification,
CFPProcessEquipmentTooling,
CFPProcessMaterialConfirmation,
CFPProcessMethodConfirmation,
CFPProductPerformance,
CFPProductContinuityTest,
CFPProductHandfeel,
CFPProductPackagingInspection,
CFPProductBarcodeScan,
CFPFinalConclusion,
CFPWhichInspectionQualified,
CFPNonconformanceDescription1,
CFPNonconformanceImage1,
CFPNonconformanceDescription2,
CFPNonconformanceImage2,
CFPNonconformanceDescription3,
CFPNonconformanceImage3,
CFPNonconformanceDescription4,
CFPNonconformanceImage4,
'' as ImageUrl,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,CFPCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY CFPCreateTime desc) AS RowNum  
from ColaFirstPiecePatrolInspectionRecord {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "CFPInspector");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "CFPShift");
                                worksheet.Cells[currentRow, 9].Value = DateTimeBackValue(reader, "CFPInspectionTime");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "CFPLineParting");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "CFPInspectionTiming");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "CFPProductModel");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "CFPOrderNumber");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "CFPInspectionItem");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "CFPProductMarking");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "CFPProductAppearance");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "CFPProductAssemblyInspection");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "CFPProcessPersonnelQualification");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "CFPProcessEquipmentTooling");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "CFPProcessMaterialConfirmation");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "CFPProcessMethodConfirmation");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "CFPProductPerformance");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "CFPProductContinuityTest");
                                worksheet.Cells[currentRow, 24].Value = StringBackValue(reader, "CFPProductHandfeel");
                                worksheet.Cells[currentRow, 25].Value = StringBackValue(reader, "CFPProductPackagingInspection");
                                worksheet.Cells[currentRow, 26].Value = StringBackValue(reader, "CFPProductBarcodeScan");
                                worksheet.Cells[currentRow, 27].Value = StringBackValue(reader, "CFPFinalConclusion");
                                worksheet.Cells[currentRow, 28].Value = StringBackValue(reader, "CFPWhichInspectionQualified");
                                worksheet.Cells[currentRow, 29].Value = StringBackValue(reader, "CFPNonconformanceDescription1");
                                ExcelHyperlink(worksheet.Cells[currentRow, 30], reader, "CFPNonconformanceImage1");
                                worksheet.Cells[currentRow, 31].Value = StringBackValue(reader, "CFPNonconformanceDescription2");
                                ExcelHyperlink(worksheet.Cells[currentRow, 32], reader, "CFPNonconformanceImage2");
                                worksheet.Cells[currentRow, 33].Value = StringBackValue(reader, "CFPNonconformanceDescription3");
                                ExcelHyperlink(worksheet.Cells[currentRow, 34], reader, "CFPNonconformanceImage3");
                                worksheet.Cells[currentRow, 35].Value = StringBackValue(reader, "CFPNonconformanceDescription4");
                                ExcelHyperlink(worksheet.Cells[currentRow, 36], reader, "CFPNonconformanceImage4");
                                worksheet.Cells[currentRow, 37].Value = StringBackValue(reader, "StrNumber");
                                worksheet.Cells[currentRow, 38].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 39].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 40].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 41].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 42].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 43].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 44].Value = StringBackValue(reader, "CFPCreateCreator");
                                worksheet.Cells[currentRow, 45].Value = StringBackValue(reader, "FillingMethod");
                                worksheet.Cells[currentRow, 1, currentRow, 45].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 45].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 45].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 45].Style.WrapText = true;
                                currentRow++;

                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Cola终检检验记录  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void ColaFinalInspectionRecord(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D559Cola 终检检验记录"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D559Cola 终检检验记录");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 44; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:AR1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:AR1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:AR1"].Style.Font.Bold = true;
            worksheet.Cells["A1:AR1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:AR1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:AR1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:AR1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:AR1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AR1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AR1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AR1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "检验员";
            worksheet.Cells["H1"].Value = "班别";
            worksheet.Cells["I1"].Value = "检验时间";
            worksheet.Cells["J1"].Value = "线别";
            worksheet.Cells["K1"].Value = "订单号";
            worksheet.Cells["L1"].Value = "产品型号";
            worksheet.Cells["M1"].Value = "批量数";
            worksheet.Cells["N1"].Value = "抽检数";
            worksheet.Cells["O1"].Value = "检验项目";
            worksheet.Cells["P1"].Value = "C1通断不良";
            worksheet.Cells["Q1"].Value = "C2保护门不回弹";
            worksheet.Cells["R1"].Value = "C3手感不良";
            worksheet.Cells["S1"].Value = "C4外观不良";
            worksheet.Cells["T1"].Value = "C5移印镭雕不良";
            worksheet.Cells["U1"].Value = "C6装配不良";
            worksheet.Cells["V1"].Value = "C7设备/夹具引起不良";
            worksheet.Cells["W1"].Value = "C8标志不良";
            worksheet.Cells["X1"].Value = "C9注塑件不良";
            worksheet.Cells["Y1"].Value = "C10包装不良";
            worksheet.Cells["Z1"].Value = "C11耐压不良";
            worksheet.Cells["AA1"].Value = "C12其他不良";
            worksheet.Cells["AB1"].Value = "检验结论";
            worksheet.Cells["AC1"].Value = "第几次检验不合格";
            worksheet.Cells["AD1"].Value = "不合格描述1（5W2H）";
            worksheet.Cells["AE1"].Value = "不合格图片1";
            worksheet.Cells["AF1"].Value = "不合格描述2（5W2H）";
            worksheet.Cells["AG1"].Value = "不合格图片2";
            worksheet.Cells["AH1"].Value = "不合格描述3（5W2H）";
            worksheet.Cells["AI1"].Value = "不合格图片3";
            worksheet.Cells["AJ1"].Value = "图片链接";
            worksheet.Cells["AK1"].Value = "处理进度";
            worksheet.Cells["AL1"].Value = "处理人";
            worksheet.Cells["AM1"].Value = "处理时间";
            worksheet.Cells["AN1"].Value = "后续动态";
            worksheet.Cells["AO1"].Value = "来源";
            worksheet.Cells["AP1"].Value = "主记录编号";
            worksheet.Cells["AQ1"].Value = "填表人";
            worksheet.Cells["AR1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND CFIRCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select CFIRID HeadID,CFIRCreateTime HeadDate,'Cola终检检验记录' as HeadFormName,'D559' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
CFIRInspector,
CFIRShift,
CFIRInspectionTime,
CFIRLineParting,
CFIROrderNumber,
CFIRProductModel,
CFIRBatchQuantity,
CFIRSampleQuantity,
CFIRInspectionItem,
CFIRC1DisconnectionDefect,
CFIRC2ProtectionGateDefect,
CFIRC3HandFeelDefect,
CFIRC4AppearanceDefect,
CFIRC5PadPrintingLaserDefect,
CFIRC6AssemblyDefect,
CFIRC7EquipmentJigDefect,
CFIRC8MarkingDefect,
CFIRC9InjectionMoldedPartDefect,
CFIRC10PackagingDefect,
CFIRC11VoltageResistanceDefect,
CFIRC12OtherDefects,
CFIRInspectionConclusion,
CFIRWhichInspectionQualified,
CFIRNonconformanceDescription1,
CFIRNonconformanceImage1,
CFIRNonconformanceDescription2,
CFIRNonconformanceImage2,
CFIRNonconformanceDescription3,
CFIRNonconformanceImage3,
'' as ImageUrl,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,CFIRCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY CFIRCreateTime desc) AS RowNum  
from ColaFinalInspectionRecord {whereClause}";

                var parameters = new List<SqlParameter>
                {

                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "CFIRInspector");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "CFIRShift");
                                worksheet.Cells[currentRow, 9].Value = DateTimeBackValue(reader, "CFIRInspectionTime");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "CFIRLineParting");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "CFIROrderNumber");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "CFIRProductModel");
                                worksheet.Cells[currentRow, 13].Value = IntBackValue(reader, "CFIRBatchQuantity");
                                worksheet.Cells[currentRow, 14].Value = IntBackValue(reader, "CFIRSampleQuantity");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "CFIRInspectionItem");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "CFIRC1DisconnectionDefect");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "CFIRC2ProtectionGateDefect");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "CFIRC3HandFeelDefect");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "CFIRC4AppearanceDefect");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "CFIRC5PadPrintingLaserDefect");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "CFIRC6AssemblyDefect");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "CFIRC7EquipmentJigDefect");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "CFIRC8MarkingDefect");
                                worksheet.Cells[currentRow, 24].Value = StringBackValue(reader, "CFIRC9InjectionMoldedPartDefect");
                                worksheet.Cells[currentRow, 25].Value = StringBackValue(reader, "CFIRC10PackagingDefect");
                                worksheet.Cells[currentRow, 26].Value = StringBackValue(reader, "CFIRC11VoltageResistanceDefect");
                                worksheet.Cells[currentRow, 27].Value = StringBackValue(reader, "CFIRC12OtherDefects");
                                worksheet.Cells[currentRow, 28].Value = StringBackValue(reader, "CFIRInspectionConclusion");
                                worksheet.Cells[currentRow, 29].Value = StringBackValue(reader, "CFIRWhichInspectionQualified");

                                worksheet.Cells[currentRow, 30].Value = StringBackValue(reader, "CFIRNonconformanceDescription1");
                                ExcelHyperlink(worksheet.Cells[currentRow, 31], reader, "CFIRNonconformanceImage1");
                                worksheet.Cells[currentRow, 32].Value = StringBackValue(reader, "CFIRNonconformanceDescription2");
                                ExcelHyperlink(worksheet.Cells[currentRow, 33], reader, "CFIRNonconformanceImage2");
                                worksheet.Cells[currentRow, 34].Value = StringBackValue(reader, "CFIRNonconformanceDescription3");
                                ExcelHyperlink(worksheet.Cells[currentRow, 35], reader, "CFIRNonconformanceImage3");

                                ExcelHyperlink(worksheet.Cells[currentRow, 36], reader, "ImageUrl");
                                worksheet.Cells[currentRow, 37].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 38].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 39].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 40].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 41].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 42].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 43].Value = StringBackValue(reader, "CFIRCreateCreator");
                                worksheet.Cells[currentRow, 44].Value = StringBackValue(reader, "FillingMethod");
                                worksheet.Cells[currentRow, 1, currentRow, 44].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 44].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 44].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 44].Style.WrapText = true;
                                currentRow++;

                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Cola 产品功能测试  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void ColaProductFunctionTest(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D587Cola 产品功能测试"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D587Cola 产品功能测试");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 27; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:AA1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:AA1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:AA1"].Style.Font.Bold = true;
            worksheet.Cells["A1:AA1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:AA1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:AA1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:AA1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:AA1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AA1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AA1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:AA1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "日期";
            worksheet.Cells["H1"].Value = "线体";
            worksheet.Cells["I1"].Value = "产品型号";
            worksheet.Cells["J1"].Value = "订单型号";
            worksheet.Cells["K1"].Value = "检验员";
            worksheet.Cells["L1"].Value = "检查项";
            worksheet.Cells["M1"].Value = "端子扭力";
            worksheet.Cells["N1"].Value = "整体拔出力";
            worksheet.Cells["O1"].Value = "模拟安装";
            worksheet.Cells["P1"].Value = "高压测试";
            worksheet.Cells["Q1"].Value = "探针测试（插座）";
            worksheet.Cells["R1"].Value = "接触规/不接触规（插座）";
            worksheet.Cells["S1"].Value = "图片链接";
            worksheet.Cells["T1"].Value = "处理进度";
            worksheet.Cells["U1"].Value = "处理人";
            worksheet.Cells["V1"].Value = "处理时间";
            worksheet.Cells["W1"].Value = "后续动态";
            worksheet.Cells["X1"].Value = "来源";
            worksheet.Cells["Y1"].Value = "主记录编号";
            worksheet.Cells["Z1"].Value = "填表人";
            worksheet.Cells["AA1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND CPFTCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select CPFTID HeadID,CPFTCreateTime HeadDate,'Cola 产品功能测试' as HeadFormName,'D587' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
CPFTDate,
CPFTLineBody,
CPFTProductModel,
CPFTOrderModel,
CPFTInspector,
CPFTInspectionItem,
CPFTTerminalTorque,
CPFTOverallPulloutForce,
CPFTSimulationInstallation,
CPFTHighVoltageTest,
CPFTProbeTest,
CPFTContactGaugeNonContactGaugeSocket,
'' as ImageUrl,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,CPFTCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY CPFTCreateTime desc) AS RowNum  
from ColaProductFunctionTest {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = DateTimeBackValue(reader, "CPFTDate");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "CPFTLineBody");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "CPFTProductModel");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "CPFTOrderModel");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "CPFTInspector");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "CPFTInspectionItem");
                                worksheet.Cells[currentRow, 13].Value = StringBackValue(reader, "CPFTTerminalTorque");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "CPFTOverallPulloutForce");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "CPFTSimulationInstallation");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "CPFTHighVoltageTest");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "CPFTProbeTest");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "CPFTContactGaugeNonContactGaugeSocket");
                                ExcelHyperlink(worksheet.Cells[currentRow, 19], reader, "ImageUrl");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 23].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 24].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 25].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 26].Value = StringBackValue(reader, "CPFTCreateCreator");
                                worksheet.Cells[currentRow, 27].Value = StringBackValue(reader, "FillingMethod");
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 27].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }

        /// <summary>
        /// Cola产线包装后全检  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void ColaPackingFinalInspection(string StartTime, string EndTime)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D562包装后全检记录"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D562包装后全检记录");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 22; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:V1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:V1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:V1"].Style.Font.Bold = true;
            worksheet.Cells["A1:V1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:V1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:V1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:V1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:V1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:V1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:V1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:V1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "姓名";
            worksheet.Cells["H1"].Value = "时间";
            worksheet.Cells["I1"].Value = "产品型号";
            worksheet.Cells["J1"].Value = "订单号";
            worksheet.Cells["K1"].Value = "线体";
            worksheet.Cells["L1"].Value = "全检数量";
            worksheet.Cells["M1"].Value = "不良数量";
            worksheet.Cells["N1"].Value = "不良原因+数量";
            worksheet.Cells["O1"].Value = "处理进度";
            worksheet.Cells["P1"].Value = "处理人";
            worksheet.Cells["Q1"].Value = "处理时间";
            worksheet.Cells["R1"].Value = "后续动态";
            worksheet.Cells["S1"].Value = "来源";
            worksheet.Cells["T1"].Value = "主记录编号";
            worksheet.Cells["U1"].Value = "填表人";
            worksheet.Cells["V1"].Value = "填写方式";


            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND CPFICreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                var query = $@"
select CPFIID HeadID,CPFICreateTime HeadDate,'Cola产线包装后全检' as HeadFormName,'D562' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
CPFIName,
CPFITime,
CPFIProductModel,
CPFIOrderNumber,
CPFILineBody,
CPFITotalInspectionQuantity,
CPFIDefectiveQuantity,
CPFIDefectiveReasonAndQuantity,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,CPFICreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY CPFICreateTime desc) AS RowNum  
from ColaPackingFinalInspection {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = StringBackValue(reader, "CPFIName");
                                worksheet.Cells[currentRow, 8].Value = DateTimeBackValue(reader, "CPFITime");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "CPFIProductModel");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "CPFIOrderNumber");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "CPFILineBody");
                                worksheet.Cells[currentRow, 12].Value = IntBackValue(reader, "CPFITotalInspectionQuantity");
                                worksheet.Cells[currentRow, 13].Value = IntBackValue(reader, "CPFIDefectiveQuantity");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "CPFIDefectiveReasonAndQuantity");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "CPFICreateCreator");
                                worksheet.Cells[currentRow, 22].Value = StringBackValue(reader, "FillingMethod");

                                worksheet.Cells[currentRow, 1, currentRow, 22].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 22].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 22].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 22].Style.WrapText = true;
                                currentRow++;

                            }
                        }

                    }
                }
            }
        }

        /// <summary>
        /// QC抽检成品边框  Sheet页生成
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        private static void QCSamplingFinishedFrame(string StartTime, string EndTime,string FormType)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D595QC抽检成品边框"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D595QC抽检成品边框");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 21; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:U1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:U1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:U1"].Style.Font.Bold = true;
            worksheet.Cells["A1:U1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:U1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:U1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:U1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:U1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:U1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:U1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:U1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "检验日期";
            worksheet.Cells["H1"].Value = "检验员";
            worksheet.Cells["I1"].Value = "检验型号";
            worksheet.Cells["J1"].Value = "检验时机";
            worksheet.Cells["K1"].Value = "抽检数量";
            worksheet.Cells["L1"].Value = "照片上传";
            worksheet.Cells["M1"].Value = "图片链接";
            worksheet.Cells["N1"].Value = "处理进度";
            worksheet.Cells["O1"].Value = "处理人";
            worksheet.Cells["P1"].Value = "处理时间";
            worksheet.Cells["Q1"].Value = "后续动态";
            worksheet.Cells["R1"].Value = "来源";
            worksheet.Cells["S1"].Value = "主记录编号";
            worksheet.Cells["T1"].Value = "填表人";
            worksheet.Cells["U1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND QCSCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                if (FormType == "F2")
                {
                    whereClause += " AND QCSFormType = 'TS A1线 面盖物料确认' ";
                }
                else
                {
                    whereClause += " AND QCSFormType = 'TS Cola生产自检'";
                }
                var query = $@"
select QCSID HeadID,QCSCreateTime HeadDate,'QC抽检成品边框' as HeadFormName,'D595' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
QCSInspectionDate,
QCSInspector,
QCSInspectionModel,
QCSInspectionTiming ,
QCSSampleQuantity,
QCSPhotoUpload,
'' as ImageUrl,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,QCSCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY QCSCreateTime desc) AS RowNum  
from QCSamplingFinishedFrame {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = DateTimeBackValue(reader, "QCSInspectionDate");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "QCSInspector");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "QCSInspectionModel");
                                worksheet.Cells[currentRow, 10].Value = StringBackValue(reader, "QCSInspectionTiming");
                                worksheet.Cells[currentRow, 11].Value = IntBackValue(reader, "QCSSampleQuantity");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "ImageUrl");
                                ExcelHyperlink(worksheet.Cells[currentRow, 13], reader, "QCSPhotoUpload");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "QCSCreateCreator");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "FillingMethod");

                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.WrapText = true;
                                currentRow++;

                            }
                        }
                    }
                }
            }
        }

        private static void A1A2EmployeeFrameFeedingConfirmation(string StartTime, string EndTime,string FormType)
        {
            // 检查Sheet是否存在
            var worksheet = package.Workbook.Worksheets["D594A1/A2员工边框上料确认(自动)"];
            if (worksheet == null)
            {
                // 添加一个新的工作表
                worksheet = package.Workbook.Worksheets.Add("D594A1/A2员工边框上料确认(自动)");
            }
            // 设置表头样式
            worksheet.Row(1).Height = 30;
            for (int i = 0; i < 21; i++)
            {
                worksheet.Column(i + 1).Width = 18;
            }
            worksheet.Cells["A1:U1"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A1:U1"].Style.Fill.BackgroundColor.SetColor(Color.SeaGreen);
            worksheet.Cells["A1:U1"].Style.Font.Bold = true;
            worksheet.Cells["A1:U1"].Style.Font.Name = "宋体";
            worksheet.Cells["A1:U1"].Style.Font.Color.SetColor(Color.White);
            worksheet.Cells["A1:U1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            worksheet.Cells["A1:U1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["A1:U1"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:U1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:U1"].Style.Border.Left.Style = ExcelBorderStyle.Thin;
            worksheet.Cells["A1:U1"].Style.Border.Right.Style = ExcelBorderStyle.Thin;
            // 设置表头文本                                                 
            worksheet.Cells["A1"].Value = "记录编号";
            worksheet.Cells["B1"].Value = "填表时间";
            worksheet.Cells["C1"].Value = "表单名称";
            worksheet.Cells["D1"].Value = "表单编号";
            worksheet.Cells["E1"].Value = "二维码名称";
            worksheet.Cells["F1"].Value = "二维码地址";
            worksheet.Cells["G1"].Value = "上料日期";
            worksheet.Cells["H1"].Value = "确认时机";
            worksheet.Cells["I1"].Value = "上料员";
            worksheet.Cells["J1"].Value = "上料数量";
            worksheet.Cells["K1"].Value = "成品型号";
            worksheet.Cells["L1"].Value = "线体";
            worksheet.Cells["M1"].Value = "照片上传";
            worksheet.Cells["N1"].Value = "处理进度";
            worksheet.Cells["O1"].Value = "处理人";
            worksheet.Cells["P1"].Value = "处理时间";
            worksheet.Cells["Q1"].Value = "后续动态";
            worksheet.Cells["R1"].Value = "来源";
            worksheet.Cells["S1"].Value = "主记录编号";
            worksheet.Cells["T1"].Value = "填表人";
            worksheet.Cells["U1"].Value = "填写方式";

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
            {
                connection.Open();
                var whereClause = "WHERE 1=1";
                if (StartTime != "全部")
                {
                    whereClause += $" AND FFCCreateTime between '{StartTime} 00:00:00' and '{EndTime} 23:59:59'";
                }
                if (FormType == "F2")
                {
                    whereClause += " AND FFCFormType = 'TS A1线 面盖物料确认'";
                }
                else
                {
                    whereClause += " AND FFCFormType = 'TS Cola生产自检'";
                }
                var query = $@"
select FFCID HeadID,FFCCreateTime HeadDate,'A1/A2员工边框上料确认' as HeadFormName,'D594' as FormNumber,
'TS Cola 生产自检, QC首件/巡检/终检记录' as QRCodeName,'http://qr61.cn/oSuaEP/qNWrYEZ' as QRCodeUrl,
FFCFeedingDate,
FFCConfirmationTiming,
FFCFeeder,
FFCFeedingQuantity,
FFCProductModel,
FFCLineBody,
FFCPhotoUpload,
'' as ProcessingProgress,'' as DealingPeople,'' as ProcessingTime,
'' as SubsequentDynamics,'手机填写' as DataSource,'' as MasterRecordNumber,FFCCreateCreator,'微信扫一扫' as FillingMethod,
'ProductAssociationDate' as HeadForm,COUNT(*) OVER() AS TotalCount,ROW_NUMBER() OVER(ORDER BY FFCCreateTime desc) AS RowNum  
from A1A2EmployeeFrameFeedingConfirmation {whereClause}";

                var parameters = new List<SqlParameter>
                {
                    //new SqlParameter("@StartTime", SqlDbType.NVarChar) { Value = StartTime },
                    //new SqlParameter("@EndTime", SqlDbType.NVarChar) { Value = EndTime }
                };
                using (var command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddRange(parameters.ToArray());
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            int currentRow = 2;
                            while (reader.Read())
                            {
                                if (currentRow == 2)
                                {
                                    DQNum += reader.GetInt32(reader.GetOrdinal("TotalCount"));
                                }
                                worksheet.Row(currentRow).Height = 47;
                                worksheet.Cells[currentRow, 1].Value = "L" + StringBackValue(reader, "HeadID");
                                worksheet.Cells[currentRow, 2].Value = DateTimeBackValue(reader, "HeadDate");
                                worksheet.Cells[currentRow, 3].Value = StringBackValue(reader, "HeadFormName");
                                worksheet.Cells[currentRow, 4].Value = StringBackValue(reader, "FormNumber");
                                worksheet.Cells[currentRow, 5].Value = StringBackValue(reader, "QRCodeName");
                                ExcelHyperlink(worksheet.Cells[currentRow, 6], reader, "QRCodeUrl");
                                worksheet.Cells[currentRow, 7].Value = DateTimeBackValue(reader, "FFCFeedingDate");
                                worksheet.Cells[currentRow, 8].Value = StringBackValue(reader, "FFCConfirmationTiming");
                                worksheet.Cells[currentRow, 9].Value = StringBackValue(reader, "FFCFeeder");
                                worksheet.Cells[currentRow, 10].Value = IntBackValue(reader, "FFCFeedingQuantity");
                                worksheet.Cells[currentRow, 11].Value = StringBackValue(reader, "FFCProductModel");
                                worksheet.Cells[currentRow, 12].Value = StringBackValue(reader, "FFCLineBody");
                                ExcelHyperlink(worksheet.Cells[currentRow, 13], reader, "FFCPhotoUpload");
                                worksheet.Cells[currentRow, 14].Value = StringBackValue(reader, "ProcessingProgress");
                                worksheet.Cells[currentRow, 15].Value = StringBackValue(reader, "DealingPeople");
                                worksheet.Cells[currentRow, 16].Value = StringBackValue(reader, "ProcessingTime");
                                worksheet.Cells[currentRow, 17].Value = StringBackValue(reader, "SubsequentDynamics");
                                worksheet.Cells[currentRow, 18].Value = StringBackValue(reader, "DataSource");
                                worksheet.Cells[currentRow, 19].Value = StringBackValue(reader, "MasterRecordNumber");
                                worksheet.Cells[currentRow, 20].Value = StringBackValue(reader, "FFCCreateCreator");
                                worksheet.Cells[currentRow, 21].Value = StringBackValue(reader, "FillingMethod");

                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.Font.Name = "宋体";
                                worksheet.Cells[currentRow, 1, currentRow, 21].Style.WrapText = true;
                                currentRow++;

                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 数据库整数字段值转换方法
        /// </summary>
        /// <param name="SDR"></param>
        /// <param name="Field"></param>
        /// <returns></returns>
        public static int IntBackValue(SqlDataReader SDR, string Field)
        {
            try
            {
                return SDR.GetInt32(SDR.GetOrdinal(Field));
            }
            catch (Exception)
            {
                return 0;
            }
        }
        /// <summary>
        /// 数据库字符串字段值转换方法
        /// </summary>
        /// <param name="SDR"></param>
        /// <param name="Field"></param>
        /// <returns></returns>
        public static string StringBackValue(SqlDataReader SDR, string Field)
        {
            try
            {
                if (Field == "HeadID")
                {
                    return SDR.GetInt32(SDR.GetOrdinal(Field)).ToString("0000000");
                }
                else
                {
                    return SDR.GetString(SDR.GetOrdinal(Field));
                }
            }
            catch (Exception)
            {
                return "";
            }
        }
        /// <summary>
        /// 数据库日期字段值转换方法
        /// </summary>
        /// <param name="SDR"></param>
        /// <param name="Field"></param>
        /// <returns></returns>
        public static string DateTimeBackValue(SqlDataReader SDR, string Field)
        {
            try
            {
                return SDR.GetDateTime(SDR.GetOrdinal(Field)).ToString("yyyy-MM-dd HH:mm:ss");
            }
            catch (Exception)
            {
                return "";
            }
        }

        /// <summary>
        /// 数据库图片路径转换方法
        /// </summary>
        /// <param name="ER"></param>
        /// <param name="SDR"></param>
        /// <param name="Field"></param>
        /// <returns></returns>
        public static ExcelRange ExcelHyperlink(ExcelRange ER, SqlDataReader SDR, string Field)
        {
            try
            {
                ER.Value = StringBackValue(SDR, Field).Replace(";", "\n");
                if (StringBackValue(SDR, Field) != "")
                {
                    try
                    {
                        ER.Hyperlink = new Uri(StringBackValue(SDR, Field).Replace(";", "\n").Split(';')[0]); // 设置为超链接
                        ER.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
                        ER.Style.Font.UnderLine = true;
                        return ER;
                    }
                    catch (Exception)
                    {
                        ER.Value = "该记录无图片";
                        return ER;
                    }
                }
                else
                {
                    ER.Value = "该记录无图片";
                    return ER;
                }
            }
            catch (Exception)
            {
                ER.Value = "该记录无图片";
                return ER;
            }
        }
    }
}
